package com.ws.shop.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.stereotype.Repository;

import com.ws.shop.bean.Page;
import com.ws.shop.dao.ExpressDao;
import com.ws.shop.entity.Express;
import com.ws.shop.util.JdbcUtil;

@Repository(value="expressDao")
public class ExpressDaoImpl extends BaseDaoImpl implements ExpressDao {
	private static Logger log = Logger.getLogger(ExpressDaoImpl.class);
	
	final String findExpressListPage_count = "select count(1) from jvf_express";
	final String findExpressListPage_list = "select id,name,aid,oid,goodsname,company,number,addtime from jvf_express";
	final String findExpressListPage_order = " order by id";
	public Page findExpressListPage(long pageno,int pagesize, String name, String start,
			String end) {
		StringBuilder query = new StringBuilder(" where 1=1");
		if(end != null){
			query.append(" and addtime<='").append(end).append(" 23:59:59'");
		}
		if(start != null){
			query.append(" and addtime>='").append(start).append(" 00:00:00'");
		}
		if(name != null){
			query.append(" and name='").append(name).append("'");
		}
		return findPage(pageno,pagesize,new StringBuilder(findExpressListPage_count).append(query).toString()
				,new StringBuilder(findExpressListPage_list).append(query).append(findExpressListPage_order));
	}
	
	final String deleteExpressById = "delete from jvf_express where id=?";
	public void deleteExpressById(int id) {
		this.update(deleteExpressById, id);
	}
	
	final String findExpressById = "select name,aid,oid,goodsname,company,number,addtime from jvf_express where id=?";
	public Express findExpressById(final int id) {
		return this.execute(findExpressById, new PreparedStatementCallback<Express>(){
			public Express doInPreparedStatement(PreparedStatement ps)
					throws SQLException, DataAccessException {
				ResultSet rs = null;
				try{
					ps.setInt(1, id);
					rs = ps.executeQuery();
					if(rs.next()){
						return new Express(id,
								rs.getString(1),
								rs.getInt(2),
								rs.getInt(3),
								rs.getString(4),
								rs.getString(5),
								rs.getString(6),
								rs.getTimestamp(7));
					}
					return null;
				}finally{
					JdbcUtil.closeResultSet(rs);
				}
			}
		});
	}
	
	final String insertExpress = "insert into jvf_express(name,aid,oid,goodsname,company,number,addtime) values(?,?,?,?,?,?,now())";
	final String updateExpress = "update jvf_express set name=?,goodsname=?,company=?,number=?,addtime=now() where id=?";
	public void saveOrUpdateExpress(Express express) {
		if(express.getId() == 0){
			this.update(insertExpress, express.getName(),express.getAid(),express.getOid(),express.getGoodsname(),express.getCompany(),express.getNumber());
		}else{
			this.update(updateExpress, express.getName(),express.getGoodsname(),express.getCompany(),express.getNumber(),express.getId());
		}
	}

	
	public void findExpress(final Page page){
		 this.execute(
					new ConnectionCallback() {
						public Object doInConnection(Connection connect) {
							PreparedStatement psmt = null;
							ResultSet rs = null;
							try {
								String s = "SELECT COUNT(1) FROM jvf_express";
								psmt = connect.prepareStatement(s);
								rs = psmt.executeQuery();
								long totalnum = 0;
								if(rs.next()){
									totalnum = rs.getLong(1);
								}
								JdbcUtil.close(rs,psmt,null);
								if(totalnum!=0){
								   s = "select name,aid,oid,goodsname,company,number,addtime from  jvf_express limit?,?";
								   psmt = connect.prepareStatement(s);
								   psmt.setLong(1, page.getStartrownum());
								   psmt.setInt(2, page.getPagesize());
									rs = psmt.executeQuery();
									while (rs.next()) {
									    Express express = new Express();
									    express.setName(rs.getString(1));
									    express.setAid(rs.getInt(2));
									    express.setOid(rs.getInt(3));
									    express.setGoodsname(rs.getString(4));
									    express.setCompany(rs.getString(5));
									    express.setNumber(rs.getString(6));
									    express.setAddtime(rs.getTimestamp(7));
										page.addData(express);
									}
								}
								return null;
							} catch (Exception e) {
								e.printStackTrace();
								log.error("获取发货公司信息!",e);
								return null;
							} finally {
								JdbcUtil.close(rs, psmt, null);
				      		}
				}
			});
	}
	
	
	public List<Map<String,Object>> getExpressByAid(long aid){
		String s = "select name,aid,oid,goodsname,company,number,addtime from  jvf_express where aid = " + aid;
		return this.queryForList(s);
	}
	
}
